#!/usr/bin/python
# -*- coding: utf-8 -*-
# @author: 高建平
# @description: 
#
from sqlalchemy import Result,Column,Table,MetaData,Select,case
from sqlalchemy.orm import Session
from sqlalchemy.sql.functions import Function,func
from sqlalchemy.sql.expression import select,and_,table,column,tuple_,text,exists
from fastapi import Depends
from app.models.dto.project_sale_strategy import ProjectSaleStrategyQueryReq
from app.models.po.project_sale_strategy_po import ProjectSaleStrategyPo
from app.models.po.sub_project import SubProject
from app.models.po.project_base_po import ProjectBasePo
from app.models.po.project_planning_task_po import ProjectPlanningTaskPo
from app.providers.database_provider import get_db
import json
import datetime
 
class ProjectSaleStrategyDao():
    
    def select_sub_project_list(self,request: ProjectSaleStrategyQueryReq,db: Session):
        '''
         查询子项目策略
        '''
        # 方法一
        return db.query(ProjectSaleStrategyPo).filter(ProjectSaleStrategyPo.sub_project_code==request.subProjectCode).filter(ProjectSaleStrategyPo.snapshot==0).offset(request.offset).limit(request.pageSize).all()
        
        # 方法二
        # stmt = select(ProjectSaleStrategyPo).where(and_(ProjectSaleStrategyPo.sub_project_code==request.subProjectCode,ProjectSaleStrategyPo.snapshot==0)).offset(request.offset).limit(request.pageSize)
        # list = []
        # for raw in db.execute(stmt).fetchall():
        #     list.append(raw[0])
        # return list
        
    
    def select_sub_project_snapshot_list(self,request: ProjectSaleStrategyQueryReq,db: Session):
        '''
         查询子项目策略历史快照列表
        '''
        # 方法一
        # return db.query(ProjectSaleStrategyPo).filter(ProjectSaleStrategyPo.snapshot==request.id).offset(request.offset).limit(request.pageSize).all()
        stmt = select(ProjectSaleStrategyPo).where(ProjectSaleStrategyPo.snapshot==request.id).offset(request.offset).limit(request.pageSize)
        return db.execute(stmt).all()
    
    
    def select_sub_project_panel_list(self,request: ProjectSaleStrategyQueryReq,db: Session):
        '''
         查询子项目看板
        '''  
        stmt = select(ProjectSaleStrategyPo,SubProject).join(SubProject,ProjectSaleStrategyPo.sub_project_code==SubProject.subproject_id)
        
        stmt = stmt.where(ProjectSaleStrategyPo.snapshot == 0)
        stmt = stmt.where(ProjectSaleStrategyPo.sale_strategy_code.__eq__(request.saleStrategyCode))
        stmt = stmt.where(ProjectSaleStrategyPo.start_date.__le__(datetime.date.today()))
        stmt = stmt.where(ProjectSaleStrategyPo.end_date.__ge__(datetime.date.today()))
        
        if request.divisionCodeList and len(request.divisionCodeList)>0:
            stmt = stmt.where(ProjectSaleStrategyPo.division_code.in_(request.divisionCodeList))
        if request.regionCodeList and len(request.regionCodeList)>0:
            stmt = stmt.where(ProjectSaleStrategyPo.region_code.in_(request.regionCodeList))
        return db.execute(stmt).all()
    
    def select_to_first_launch_project_panel(self,request: ProjectSaleStrategyQueryReq,db: Session):
        '''
         即将首发项目看版
        '''  
        metadata = MetaData()
        project_planning_task = Table('project_planning_task',metadata,
                                      Column('project_id'),
                                      Column('actual_end_time'),
                                      Column('is_deleted'),
                                      )
        project_base = Table('project_base',metadata,
                                      Column('project_id'),
                                      Column('project_name'),
                                      Column('is_deleted'),
                                      Column('data_source'),
                                      )
        
        
        stmt = Select(
                    #   ProjectSaleStrategyPo,
                    #   SubProject,
                      ProjectSaleStrategyPo.sub_project_code,
                      SubProject.subproject_name,
                      project_planning_task.c.actual_end_time,
                      SubProject.subproject_type,
                      SubProject.subproject_type_name,
                      project_base.c.project_id,
                      project_base.c.project_name,
                      )
        stmt=stmt.where(project_base.c.project_id == project_planning_task.c.project_id)
        stmt=stmt.where(project_base.c.project_id == SubProject.project_id)
        stmt=stmt.where(ProjectSaleStrategyPo.sub_project_code == SubProject.subproject_id)
        stmt=stmt.where(project_base.c.is_deleted==0)
        stmt=stmt.where(project_planning_task.c.is_deleted==0)
        stmt=stmt.where(project_base.c.data_source == 1)
        stmt=stmt.where(SubProject.is_deleted == 0)
        stmt=stmt.where(ProjectSaleStrategyPo.snapshot == 0)
        stmt=stmt.where(
            case((project_planning_task.c.actual_end_time.__ge__('2023-07-01 00:00:00'),True),
            else_=(SubProject.subproject_id.in_(['a','b']))
        ))
        stmt=stmt.where(ProjectSaleStrategyPo.sale_strategy_code.in_([1,2]))        
        return db.execute(stmt).all()
    
    def select_to_first_launch_project_panel_1(self,request: ProjectSaleStrategyQueryReq,db: Session):
        '''
         即将首发项目看版
        '''  

        stmt = Select(
                    # ProjectBasePo,
                    # ProjectPlanningTaskPo,
                    ProjectSaleStrategyPo,
                    SubProject,
                    )
        stmt=stmt.where(ProjectBasePo.project_id == ProjectPlanningTaskPo.project_id)
        stmt=stmt.where(ProjectBasePo.project_id == SubProject.project_id)
        stmt=stmt.where(ProjectSaleStrategyPo.sub_project_code == SubProject.subproject_id)
        stmt=stmt.where(ProjectBasePo.is_deleted==0)
        stmt=stmt.where(ProjectPlanningTaskPo.is_deleted==0)
        stmt=stmt.where(ProjectBasePo.data_source == 1)
        stmt=stmt.where(SubProject.is_deleted == 0)
        stmt=stmt.where(ProjectSaleStrategyPo.snapshot == 0)
        stmt=stmt.where(
            case((ProjectPlanningTaskPo.actual_end_time.__ge__('2023-07-01 00:00:00'),True),
            else_=( SubProject.subproject_id.in_(['a','b']))
        ))
        stmt=stmt.where(ProjectSaleStrategyPo.sale_strategy_code.in_([1,2]))        
        return db.execute(stmt).all()
        